This vignette assumes a SQL server at localhost (we use PostgreSQL), with data in OMOP Common Data Model v5.4 format in schema cdm_new_york3. The patient records shown in this example are synthetic data from Synthea(TM) Patient Generator.

library(phea)
library(dplyr)

# Connect to SQL server.
dbcon <- DBI::dbConnect(RPostgres::Postgres(),
  host = 'localhost', port = 7654, dbname = 'fort',
  user = cred$pg$user, password = cred$pg$pass)

# Call setup_phea so we can use sqlt() and sql0().
setup_phea(dbcon, 'cdm_new_york3')

In this vignette we identify:

Case A.

Case B.

Case C.

Here’s how we compute it:

In both cases, we will normalize the unit of measurement of serum creatinine to md/dL prior to computing formulas. We could also use formulas for that, and the result would be the same.

Create components

Serum creatinine

We collect SCr records from MEASUREMENT and convert the units to mg/dL.

# Serum creatinine codes used: 
# Loinc 38483-4 Creatinine [Mass/volume] in Blood, OMOP CDM concept ID 3051825
# Loinc 2160-0 Creatinine [Mass/volume] in Serum or Plasma, OMOP CDM concept ID 3016723

# "A" records: Unit is 'mg/dL'.
scr_records_a <- sqlt(measurement) |>
  filter(measurement_concept_id %in% c(3051825, 3016723) &&
    unit_source_value == 'mg/dL')

# "B" records: Unit is 'µmol/L', but we convert to 'mg/dL'.
scr_records_b <- sqlt(measurement) |>
  filter(measurement_concept_id %in% c(3051825, 3016723) &&
    unit_source_value == 'µmol/L') |>
  mutate( # Convert µmol/L to mg/dL
    value_as_number = value_as_number / 88.42,
    unit_source_value = 'mg/dL')

# Combine all available serum creatinine records.
scr_records <- union_all(scr_records_a, scr_records_b)

# Make a record source.
scr_record_source <- make_record_source(scr_records,
  ts = measurement_datetime,
  pid = person_id)

Glomerular filtration rate

We collect GFR records from MEASUREMENT.

# GFR codes used:
# Loinc 77147-7 Glomerular filtration rate/1.73 sq M.predicted [Volume Rate/Area] in Serum, Plasma or Blood by Creatinine-based formula (MDRD)
# OMOP CDM concept ID 46236952 
gfr_records <- sqlt(measurement) |>
  filter(measurement_concept_id == 46236952)

gfr_record_source <- make_record_source(gfr_records,
    ts = measurement_datetime,
    pid = person_id)

Calculate the phenotype

Formula scr_case_a contains the logic of case A (difference ≥0.3 mg/dL), scr_case_b contains case B (ratio ≥1.5), and gfr_case_c contains case C (two GFRs under 60 within 3-5 months).

Notice we use 48 days instead of 48 hours, and 7 months instead of 7 days. This is just to make a visually meaningful example out of the limited data that is produced by Synthea(TM). Those time intervals can be changed to “48 hours” and “7 days” to produce the real phenotype.

scr_change <- calculate_formula(
  components = list(
    # Current SCr
    scr = make_component(scr_record_source),
    
    # Minimum value within 48-hour window
    scr_48h_min = make_component(scr_record_source,
      window = '48 days', .delay_fn = 'min'),
    
    # Minimum value within 7-day window
    scr_7d_min = make_component(scr_record_source,
      window = '7 months', .delay_fn = 'min'),
    
    # Current glomerular filtration rate (GFR)
    gfr = make_component(gfr_record_source),
    
    # Glomerular filtration rate 3 to 5 months older than phenotype date
    gfr_prior = make_component(gfr_record_source,
      delay = '3 months', window = '5 months', .delay_fn = 'min')),
    
  fml = list(
    scr_case_a = 'scr_value_as_number - scr_48h_min_value_as_number >= 0.3',
    
    scr_case_b = 'scr_value_as_number / scr_7d_min_value_as_number >= 1.5',
    
    gfr_case_c = 'gfr_value_as_number < 60 AND gfr_prior_value_as_number < 60'),
  
  export = c(
    'scr_measurement_datetime',
    'scr_48h_min_measurement_datetime',
    'scr_7d_min_measurement_datetime',
    'gfr_measurement_datetime',
    'gfr_prior_measurement_datetime'),
  
  .cascaded = FALSE, # Because we don't need to use results of prior formulas inside other formulas.
)

Let us take a small peek at 15 rows from the phenotype results.

head_shot(scr_change, 15) |>
  kable()
row_id pid ts window scr_value_as_number scr_48h_min_value_as_number scr_7d_min_value_as_number gfr_value_as_number gfr_prior_value_as_number scr_measurement_datetime scr_48h_min_measurement_datetime scr_7d_min_measurement_datetime gfr_measurement_datetime gfr_prior_measurement_datetime scr_case_a scr_case_b gfr_case_c
1 1 2014-03-07 00:00:00 1.4 1.4 1.4 124.5 NA 2014-03-07 2014-03-07 2014-03-07 2014-03-07 NA FALSE FALSE FALSE
2 1 2016-03-11 00:00:00 1.3 1.3 1.3 120.9 NA 2016-03-11 2016-03-11 2016-03-11 2016-03-11 NA FALSE FALSE FALSE
3 1 2018-03-16 00:00:00 1.3 1.3 1.3 151.7 NA 2018-03-16 2018-03-16 2018-03-16 2018-03-16 NA FALSE FALSE FALSE
12 1 2020-03-20 00:00:00 1.3 1.3 1.3 107.3 NA 2020-03-20 2020-03-20 2020-03-20 2020-03-20 NA FALSE FALSE FALSE
5 1 2022-02-11 00:00:00 1.8 1.5 1.5 132.3 NA 2022-02-11 2022-02-11 2022-02-11 2022-02-11 NA TRUE FALSE FALSE
16 1 2022-03-25 42 days 1.5 1.5 1.5 84.1 NA 2022-03-25 2022-02-11 2022-02-11 2022-03-25 NA FALSE FALSE FALSE
17 3 2004-11-26 00:00:00 1.5 1.5 1.5 112.0 NA 2004-11-26 2004-11-26 2004-11-26 2004-11-26 NA FALSE FALSE FALSE
47 3 2005-12-02 00:00:00 1.5 1.5 1.5 114.0 NA 2005-12-02 2005-12-02 2005-12-02 2005-12-02 NA FALSE FALSE FALSE
19 3 2006-12-08 00:00:00 1.5 1.5 1.5 111.9 NA 2006-12-08 2006-12-08 2006-12-08 2006-12-08 NA FALSE FALSE FALSE
49 3 2007-12-14 00:00:00 1.4 1.4 1.4 131.9 NA 2007-12-14 2007-12-14 2007-12-14 2007-12-14 NA FALSE FALSE FALSE
50 3 2008-12-19 00:00:00 1.3 1.3 1.3 147.5 NA 2008-12-19 2008-12-19 2008-12-19 2008-12-19 NA FALSE FALSE FALSE
22 3 2009-12-25 00:00:00 1.3 1.3 1.3 149.6 NA 2009-12-25 2009-12-25 2009-12-25 2009-12-25 NA FALSE FALSE FALSE
23 3 2010-12-31 00:00:00 1.3 1.3 1.3 132.3 NA 2010-12-31 2010-12-31 2010-12-31 2010-12-31 NA FALSE FALSE FALSE
24 3 2011-04-15 105 days 1.4 1.4 1.3 142.0 132.3 2011-04-15 2011-04-15 2010-12-31 2011-04-15 2010-12-31 FALSE FALSE FALSE
54 3 2012-01-06 371 days 1.5 1.5 1.5 91.6 132.3 2012-01-06 2012-01-06 2012-01-06 2012-01-06 2010-12-31 FALSE FALSE FALSE

Plot the phenotype for a random patient

For demonstration purposes, let us pick an “interesting” patient. By “interesting” I just mean a patient who at different times was TRUE and FALSE in each of the three criteria. A patient with variability in their timeline.

# Find an "interesting" case to plot: patients with each criteria at times TRUE, at times FALSE
patients <- scr_change |>
  group_by(pid) |>
  summarise(
    n_03 = n_distinct(scr_case_a),
    n_15 = n_distinct(scr_case_b),
    n_gfr = n_distinct(gfr_case_c)) |>
  mutate(sort_variable = n_03 + n_15 + n_gfr) |>
  arrange(desc(sort_variable)) |>
  head(20) |>
  select(pid) |>
  pull()

random_patient <- sample(patients, 1)

message('Sampled patient: ', random_patient)
#> Sampled patient: 344

Then we plot all data for the chosen patient (pid = 344).

scr_change |>
  select(-ends_with('datetime')) |>
  phea_plot(random_patient)
#> Collecting lazy table, done. (turn this message off with `verbose = FALSE`)

At the end of this report I include a large table with all the data for this patient, directly from the record sources, for maximum verification.

Obtain the SQL query that computes the phenotype

To see the SQL query underlying the phenotype, use helper function code_shot(), or dbplyr::sql_render(), or the .clip_sql option in calculate_formula().

code_shot(scr_change)
SELECT
  "row_id",
  "pid",
  "ts",
  "window",
  "scr_value_as_number",
  "scr_48h_min_value_as_number",
  "scr_7d_min_value_as_number",
  "gfr_value_as_number",
  "gfr_prior_value_as_number",
  "scr_measurement_datetime",
  "scr_48h_min_measurement_datetime",
  "scr_7d_min_measurement_datetime",
  "gfr_measurement_datetime",
  "gfr_prior_measurement_datetime",
  scr_value_as_number - scr_48h_min_value_as_number >= 0.3 AS "scr_case_a",
  scr_value_as_number / scr_7d_min_value_as_number >= 1.5 AS "scr_case_b",
  gfr_value_as_number < 60 AND gfr_prior_value_as_number < 60 AS "gfr_case_c"
FROM (
  SELECT
    *,
    "ts" - least(scr_ts, scr_48h_min_ts, scr_7d_min_ts, gfr_ts, gfr_prior_ts) AS "window",
    last_value(row_id) over (partition by "pid", "ts") AS "ts_row"
  FROM (
    SELECT
      "row_id",
      "pid",
      "ts",
      MAX("scr_measurement_datetime") OVER (PARTITION BY "pid", "..dbplyr_partion_1") AS "scr_measurement_datetime",
      MAX("scr_value_as_number") OVER (PARTITION BY "pid", "..dbplyr_partion_2") AS "scr_value_as_number",
      MAX("scr_ts") OVER (PARTITION BY "pid", "..dbplyr_partion_3") AS "scr_ts",
      MAX("scr_48h_min_measurement_datetime") OVER (PARTITION BY "pid", "..dbplyr_partion_4") AS "scr_48h_min_measurement_datetime",
      MAX("scr_48h_min_value_as_number") OVER (PARTITION BY "pid", "..dbplyr_partion_5") AS "scr_48h_min_value_as_number",
      MAX("scr_48h_min_ts") OVER (PARTITION BY "pid", "..dbplyr_partion_6") AS "scr_48h_min_ts",
      MAX("scr_7d_min_measurement_datetime") OVER (PARTITION BY "pid", "..dbplyr_partion_7") AS "scr_7d_min_measurement_datetime",
      MAX("scr_7d_min_value_as_number") OVER (PARTITION BY "pid", "..dbplyr_partion_8") AS "scr_7d_min_value_as_number",
      MAX("scr_7d_min_ts") OVER (PARTITION BY "pid", "..dbplyr_partion_9") AS "scr_7d_min_ts",
      MAX("gfr_measurement_datetime") OVER (PARTITION BY "pid", "..dbplyr_partion_10") AS "gfr_measurement_datetime",
      MAX("gfr_value_as_number") OVER (PARTITION BY "pid", "..dbplyr_partion_11") AS "gfr_value_as_number",
      MAX("gfr_ts") OVER (PARTITION BY "pid", "..dbplyr_partion_12") AS "gfr_ts",
      MAX("gfr_prior_measurement_datetime") OVER (PARTITION BY "pid", "..dbplyr_partion_13") AS "gfr_prior_measurement_datetime",
      MAX("gfr_prior_value_as_number") OVER (PARTITION BY "pid", "..dbplyr_partion_14") AS "gfr_prior_value_as_number",
      MAX("gfr_prior_ts") OVER (PARTITION BY "pid", "..dbplyr_partion_15") AS "gfr_prior_ts"
    FROM (
      SELECT
        *,
        SUM(CASE WHEN (("scr_measurement_datetime" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_1",
        SUM(CASE WHEN (("scr_value_as_number" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_2",
        SUM(CASE WHEN (("scr_ts" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_3",
        SUM(CASE WHEN (("scr_48h_min_measurement_datetime" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_4",
        SUM(CASE WHEN (("scr_48h_min_value_as_number" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_5",
        SUM(CASE WHEN (("scr_48h_min_ts" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_6",
        SUM(CASE WHEN (("scr_7d_min_measurement_datetime" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_7",
        SUM(CASE WHEN (("scr_7d_min_value_as_number" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_8",
        SUM(CASE WHEN (("scr_7d_min_ts" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_9",
        SUM(CASE WHEN (("gfr_measurement_datetime" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_10",
        SUM(CASE WHEN (("gfr_value_as_number" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_11",
        SUM(CASE WHEN (("gfr_ts" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_12",
        SUM(CASE WHEN (("gfr_prior_measurement_datetime" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_13",
        SUM(CASE WHEN (("gfr_prior_value_as_number" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_14",
        SUM(CASE WHEN (("gfr_prior_ts" IS NULL)) THEN 0 ELSE 1 END) OVER (PARTITION BY "pid" ORDER BY "pid", "ts" ROWS UNBOUNDED PRECEDING) AS "..dbplyr_partion_15"
      FROM (
        SELECT
          row_number() over () AS "row_id",
          "pid",
          "ts",
          last_value(case when "name" = 'zk1f53rgblyz' then "measurement_datetime" else null end) over (partition by "pid", "name" order by "ts" rows between unbounded preceding and 0 preceding) AS "scr_measurement_datetime",
          last_value(case when "name" = 'zk1f53rgblyz' then "value_as_number" else null end) over (partition by "pid", "name" order by "ts" rows between unbounded preceding and 0 preceding) AS "scr_value_as_number",
          last_value(case when "name" = 'zk1f53rgblyz' then "ts" else null end) over (partition by "pid", "name" order by "ts" rows between unbounded preceding and 0 preceding) AS "scr_ts",
          min(case when "name" = 'zk1f53rgblyz' then "measurement_datetime" else null end) over (partition by "pid", "name" order by "ts" range between '48 days'::interval preceding and '0 days'::interval preceding) AS "scr_48h_min_measurement_datetime",
          min(case when "name" = 'zk1f53rgblyz' then "value_as_number" else null end) over (partition by "pid", "name" order by "ts" range between '48 days'::interval preceding and '0 days'::interval preceding) AS "scr_48h_min_value_as_number",
          min(case when "name" = 'zk1f53rgblyz' then "ts" else null end) over (partition by "pid", "name" order by "ts" range between '48 days'::interval preceding and '0 days'::interval preceding) AS "scr_48h_min_ts",
          min(case when "name" = 'zk1f53rgblyz' then "measurement_datetime" else null end) over (partition by "pid", "name" order by "ts" range between '7 months'::interval preceding and '0 days'::interval preceding) AS "scr_7d_min_measurement_datetime",
          min(case when "name" = 'zk1f53rgblyz' then "value_as_number" else null end) over (partition by "pid", "name" order by "ts" range between '7 months'::interval preceding and '0 days'::interval preceding) AS "scr_7d_min_value_as_number",
          min(case when "name" = 'zk1f53rgblyz' then "ts" else null end) over (partition by "pid", "name" order by "ts" range between '7 months'::interval preceding and '0 days'::interval preceding) AS "scr_7d_min_ts",
          last_value(case when "name" = 'gzx6hj0wfnm8' then "measurement_datetime" else null end) over (partition by "pid", "name" order by "ts" rows between unbounded preceding and 0 preceding) AS "gfr_measurement_datetime",
          last_value(case when "name" = 'gzx6hj0wfnm8' then "value_as_number" else null end) over (partition by "pid", "name" order by "ts" rows between unbounded preceding and 0 preceding) AS "gfr_value_as_number",
          last_value(case when "name" = 'gzx6hj0wfnm8' then "ts" else null end) over (partition by "pid", "name" order by "ts" rows between unbounded preceding and 0 preceding) AS "gfr_ts",
          min(case when "name" = 'gzx6hj0wfnm8' then "measurement_datetime" else null end) over (partition by "pid", "name" order by "ts" range between '5 months'::interval preceding and '3 months'::interval preceding) AS "gfr_prior_measurement_datetime",
          min(case when "name" = 'gzx6hj0wfnm8' then "value_as_number" else null end) over (partition by "pid", "name" order by "ts" range between '5 months'::interval preceding and '3 months'::interval preceding) AS "gfr_prior_value_as_number",
          min(case when "name" = 'gzx6hj0wfnm8' then "ts" else null end) over (partition by "pid", "name" order by "ts" range between '5 months'::interval preceding and '3 months'::interval preceding) AS "gfr_prior_ts"
        FROM (
          (
            SELECT
              'zk1f53rgblyz' AS "name",
              "person_id" AS "pid",
              "measurement_datetime" AS "ts",
              "measurement_datetime",
              "value_as_number"
            FROM (
              (
                SELECT *
                FROM "cdm_new_york3"."measurement"
                WHERE ("measurement_concept_id" IN (3051825.0, 3016723.0) AND "unit_source_value" = 'mg/dL')
              )
              UNION ALL
              (
                SELECT
                  "measurement_id",
                  "person_id",
                  "measurement_concept_id",
                  "measurement_date",
                  "measurement_datetime",
                  "measurement_time",
                  "measurement_type_concept_id",
                  "operator_concept_id",
                  "value_as_number" / 88.42 AS "value_as_number",
                  "value_as_concept_id",
                  "unit_concept_id",
                  "range_low",
                  "range_high",
                  "provider_id",
                  "visit_occurrence_id",
                  "visit_detail_id",
                  "measurement_source_value",
                  "measurement_source_concept_id",
                  'mg/dL' AS "unit_source_value",
                  "unit_source_concept_id",
                  "value_source_value",
                  "measurement_event_id",
                  "meas_event_field_concept_id"
                FROM "cdm_new_york3"."measurement"
                WHERE ("measurement_concept_id" IN (3051825.0, 3016723.0) AND "unit_source_value" = 'µmol/L')
              )
            ) "q01"
          )
          UNION ALL
          (
            SELECT
              'gzx6hj0wfnm8' AS "name",
              "person_id" AS "pid",
              "measurement_datetime" AS "ts",
              "measurement_datetime",
              "value_as_number"
            FROM "cdm_new_york3"."measurement"
            WHERE ("measurement_concept_id" = 46236952.0)
          )
        ) "q02"
      ) "q03"
    ) "q04"
  ) "q05"
) "q06"
WHERE ("row_id" = "ts_row")

See the raw data that went into calculate_formula()

Sometimes the best way to check a result is to manually, painstakingly go over the data. Below I print all rows from the record sources for the patient that was plotted above.

Serum creatinine records

scr_records |>
  filter(person_id == random_patient) |>
  collect() |>
  arrange(measurement_datetime) |>
  select(measurement_id, person_id, measurement_datetime, value_as_number, unit_source_value) |>
  kable()
measurement_id person_id measurement_datetime value_as_number unit_source_value
135039 344 2003-01-24 1.6 mg/dL
135299 344 2004-01-30 1.1 mg/dL
135119 344 2005-02-04 1.1 mg/dL
136678 344 2006-02-10 1.0 mg/dL
136741 344 2007-02-16 1.0 mg/dL
136828 344 2008-02-22 1.8 mg/dL
135242 344 2008-04-18 1.3 mg/dL
135259 344 2008-04-18 1.5 mg/dL
136468 344 2008-05-16 1.8 mg/dL
136490 344 2008-05-16 1.5 mg/dL
137314 344 2008-10-10 2.5 mg/dL
136428 344 2009-01-09 1.7 mg/dL
137291 344 2009-02-27 2.2 mg/dL
136585 344 2009-04-03 2.1 mg/dL
134841 344 2009-05-08 2.6 mg/dL
135672 344 2009-06-12 2.1 mg/dL
136920 344 2009-07-10 1.8 mg/dL
135513 344 2009-09-11 1.9 mg/dL
136953 344 2010-03-05 2.7 mg/dL
134833 344 2010-03-19 3.0 mg/dL
135490 344 2010-07-02 2.7 mg/dL
136770 344 2011-03-11 3.2 mg/dL
135158 344 2011-04-29 2.8 mg/dL
137419 344 2011-10-28 1.9 mg/dL
135906 344 2011-11-25 1.8 mg/dL
135949 344 2011-12-30 2.6 mg/dL
135929 344 2012-03-16 1.8 mg/dL
137205 344 2012-03-30 2.6 mg/dL
135090 344 2012-06-22 2.1 mg/dL
136972 344 2012-07-27 1.9 mg/dL
136030 344 2012-09-28 2.7 mg/dL
135590 344 2012-10-19 3.1 mg/dL
134867 344 2012-12-28 2.0 mg/dL
135969 344 2013-01-18 2.6 mg/dL
135690 344 2013-02-22 2.1 mg/dL
134932 344 2013-03-22 2.1 mg/dL
135719 344 2013-04-19 2.1 mg/dL
136991 344 2013-05-17 2.4 mg/dL
135621 344 2013-07-19 1.9 mg/dL
136539 344 2013-09-20 1.8 mg/dL
135838 344 2013-11-15 2.8 mg/dL
136896 344 2014-02-21 2.4 mg/dL
136109 344 2014-03-28 1.8 mg/dL
135654 344 2014-04-18 1.9 mg/dL
137268 344 2014-07-11 2.2 mg/dL
136135 344 2014-08-15 2.1 mg/dL
137463 344 2014-09-12 2.3 mg/dL
136236 344 2015-02-06 1.7 mg/dL
135440 344 2015-04-03 1.8 mg/dL
137526 344 2015-04-17 1.9 mg/dL
137246 344 2015-08-07 3.0 mg/dL
134925 344 2015-09-04 1.9 mg/dL
136204 344 2016-01-08 3.2 mg/dL
136392 344 2016-02-05 2.1 mg/dL
136508 344 2016-04-01 1.9 mg/dL
136052 344 2016-04-15 1.8 mg/dL
135374 344 2016-06-03 2.0 mg/dL
137492 344 2016-07-01 1.7 mg/dL
135401 344 2016-08-05 2.2 mg/dL
135362 344 2016-09-30 1.6 mg/dL
135557 344 2016-10-28 2.9 mg/dL
135436 344 2017-03-31 2.5 mg/dL
135536 344 2017-04-21 2.7 mg/dL
137057 344 2017-06-02 1.9 mg/dL
136613 344 2017-09-29 2.5 mg/dL
136864 344 2017-12-22 1.8 mg/dL
135855 344 2018-02-23 2.6 mg/dL
135801 344 2018-04-27 2.6 mg/dL
136211 344 2018-05-11 2.9 mg/dL
135765 344 2018-05-25 1.8 mg/dL
137171 344 2018-06-29 2.2 mg/dL
135599 344 2018-07-20 3.1 mg/dL
135075 344 2018-10-26 2.3 mg/dL
135873 344 2018-11-23 2.2 mg/dL
137017 344 2018-12-28 3.1 mg/dL
134808 344 2019-01-25 2.5 mg/dL
134787 344 2019-04-26 2.4 mg/dL
137153 344 2019-05-10 2.1 mg/dL
137114 344 2019-05-24 2.5 mg/dL
136850 344 2019-06-28 2.3 mg/dL
136055 344 2019-07-26 2.2 mg/dL
135190 344 2019-08-16 2.7 mg/dL
137031 344 2019-09-27 1.8 mg/dL
136077 344 2019-11-15 2.8 mg/dL
137447 344 2020-02-21 2.0 mg/dL
136673 344 2020-04-10 2.6 mg/dL
136796 344 2020-05-15 2.8 mg/dL
137333 344 2020-06-05 1.8 mg/dL
137158 344 2020-07-17 2.1 mg/dL
134890 344 2020-08-28 2.0 mg/dL
135780 344 2020-09-18 2.3 mg/dL
136356 344 2020-11-13 2.1 mg/dL
135277 344 2021-01-15 1.6 mg/dL
136562 344 2021-02-12 2.3 mg/dL
136644 344 2021-05-14 2.3 mg/dL
137208 344 2021-05-28 1.8 mg/dL
136174 344 2021-06-18 1.9 mg/dL
137522 344 2021-07-23 2.3 mg/dL
136414 344 2021-08-13 1.7 mg/dL
135060 344 2021-09-03 1.9 mg/dL
135998 344 2021-11-19 2.2 mg/dL
135220 344 2022-02-04 2.2 mg/dL
135156 344 2022-04-01 2.4 mg/dL
135331 344 2022-05-06 1.8 mg/dL
136725 344 2022-06-03 1.5 mg/dL
135746 344 2022-09-09 2.2 mg/dL
137074 344 2022-09-30 2.5 mg/dL

Glomerular filtration rate records

gfr_records |>
  filter(person_id == random_patient) |>
  collect() |>
  arrange(measurement_datetime) |>
  select(measurement_id, person_id, measurement_datetime, value_as_number, unit_source_value) |>
  kable()
measurement_id person_id measurement_datetime value_as_number unit_source_value
135027 344 2003-01-24 66.3 mL/min/{1.73_m2}
135310 344 2004-01-30 95.6 mL/min/{1.73_m2}
135129 344 2005-02-04 154.1 mL/min/{1.73_m2}
136680 344 2006-02-10 103.9 mL/min/{1.73_m2}
136750 344 2007-02-16 125.2 mL/min/{1.73_m2}
136817 344 2008-02-22 56.0 mL/min/{1.73_m2}
135253 344 2008-04-18 140.4 mL/min
135263 344 2008-04-18 74.6 mL/min/{1.73_m2}
136463 344 2008-05-16 141.7 mL/min
136472 344 2008-05-16 68.7 mL/min/{1.73_m2}
137303 344 2008-10-10 40.8 mL/min/{1.73_m2}
136440 344 2009-01-09 58.5 mL/min/{1.73_m2}
137280 344 2009-02-27 46.2 mL/min/{1.73_m2}
136575 344 2009-04-03 49.1 mL/min/{1.73_m2}
134834 344 2009-05-08 39.0 mL/min/{1.73_m2}
135681 344 2009-06-12 48.1 mL/min/{1.73_m2}
136909 344 2009-07-10 56.5 mL/min/{1.73_m2}
135524 344 2009-09-11 54.2 mL/min/{1.73_m2}
136933 344 2010-03-05 38.4 mL/min/{1.73_m2}
134822 344 2010-03-19 34.5 mL/min/{1.73_m2}
135498 344 2010-07-02 37.8 mL/min/{1.73_m2}
136768 344 2011-03-11 32.4 mL/min/{1.73_m2}
135204 344 2011-04-29 36.4 mL/min/{1.73_m2}
137407 344 2011-10-28 53.0 mL/min/{1.73_m2}
135895 344 2011-11-25 55.4 mL/min/{1.73_m2}
135960 344 2011-12-30 39.6 mL/min/{1.73_m2}
135918 344 2012-03-16 54.9 mL/min/{1.73_m2}
137194 344 2012-03-30 38.8 mL/min/{1.73_m2}
135101 344 2012-06-22 47.5 mL/min/{1.73_m2}
136941 344 2012-07-27 53.8 mL/min/{1.73_m2}
136020 344 2012-09-28 37.2 mL/min/{1.73_m2}
135592 344 2012-10-19 32.6 mL/min/{1.73_m2}
134862 344 2012-12-28 50.2 mL/min/{1.73_m2}
135980 344 2013-01-18 38.2 mL/min/{1.73_m2}
135707 344 2013-02-22 48.5 mL/min/{1.73_m2}
134941 344 2013-03-22 47.9 mL/min/{1.73_m2}
135731 344 2013-04-19 46.7 mL/min/{1.73_m2}
136993 344 2013-05-17 42.6 mL/min/{1.73_m2}
135637 344 2013-07-19 53.4 mL/min/{1.73_m2}
136533 344 2013-09-20 53.8 mL/min/{1.73_m2}
135824 344 2013-11-15 34.9 mL/min/{1.73_m2}
136887 344 2014-02-21 41.7 mL/min/{1.73_m2}
136120 344 2014-03-28 56.3 mL/min/{1.73_m2}
135642 344 2014-04-18 51.3 mL/min/{1.73_m2}
137257 344 2014-07-11 44.7 mL/min/{1.73_m2}
136124 344 2014-08-15 48.0 mL/min/{1.73_m2}
137500 344 2014-09-12 43.2 mL/min/{1.73_m2}
136249 344 2015-02-06 56.7 mL/min/{1.73_m2}
135459 344 2015-04-03 55.7 mL/min/{1.73_m2}
137537 344 2015-04-17 50.8 mL/min/{1.73_m2}
137242 344 2015-08-07 33.2 mL/min/{1.73_m2}
134903 344 2015-09-04 52.2 mL/min/{1.73_m2}
136193 344 2016-01-08 30.4 mL/min/{1.73_m2}
136381 344 2016-02-05 46.0 mL/min/{1.73_m2}
136500 344 2016-04-01 52.4 mL/min/{1.73_m2}
136037 344 2016-04-15 54.1 mL/min/{1.73_m2}
135385 344 2016-06-03 49.4 mL/min/{1.73_m2}
137470 344 2016-07-01 57.3 mL/min/{1.73_m2}
135405 344 2016-08-05 43.2 mL/min/{1.73_m2}
135335 344 2016-09-30 58.9 mL/min/{1.73_m2}
135568 344 2016-10-28 32.8 mL/min/{1.73_m2}
135424 344 2017-03-31 37.7 mL/min/{1.73_m2}
135546 344 2017-04-21 35.7 mL/min/{1.73_m2}
137068 344 2017-06-02 50.9 mL/min/{1.73_m2}
136607 344 2017-09-29 38.2 mL/min/{1.73_m2}
136873 344 2017-12-22 52.8 mL/min/{1.73_m2}
135868 344 2018-02-23 36.4 mL/min/{1.73_m2}
135803 344 2018-04-27 36.3 mL/min/{1.73_m2}
136223 344 2018-05-11 33.2 mL/min/{1.73_m2}
135763 344 2018-05-25 52.8 mL/min/{1.73_m2}
137177 344 2018-06-29 42.7 mL/min/{1.73_m2}
135613 344 2018-07-20 30.3 mL/min/{1.73_m2}
135068 344 2018-10-26 41.3 mL/min/{1.73_m2}
135885 344 2018-11-23 43.7 mL/min/{1.73_m2}
137006 344 2018-12-28 30.7 mL/min/{1.73_m2}
134796 344 2019-01-25 38.0 mL/min/{1.73_m2}
134778 344 2019-04-26 39.5 mL/min/{1.73_m2}
137139 344 2019-05-10 44.9 mL/min/{1.73_m2}
137094 344 2019-05-24 38.2 mL/min/{1.73_m2}
136840 344 2019-06-28 40.7 mL/min/{1.73_m2}
136089 344 2019-07-26 42.2 mL/min/{1.73_m2}
135201 344 2019-08-16 35.1 mL/min/{1.73_m2}
137045 344 2019-09-27 53.0 mL/min/{1.73_m2}
136060 344 2019-11-15 33.1 mL/min/{1.73_m2}
137442 344 2020-02-21 45.7 mL/min/{1.73_m2}
136670 344 2020-04-10 35.6 mL/min/{1.73_m2}
136808 344 2020-05-15 33.2 mL/min/{1.73_m2}
137341 344 2020-06-05 51.5 mL/min/{1.73_m2}
137126 344 2020-07-17 44.0 mL/min/{1.73_m2}
134881 344 2020-08-28 47.3 mL/min/{1.73_m2}
135791 344 2020-09-18 39.9 mL/min/{1.73_m2}
136372 344 2020-11-13 43.4 mL/min/{1.73_m2}
135287 344 2021-01-15 58.5 mL/min/{1.73_m2}
136572 344 2021-02-12 40.0 mL/min/{1.73_m2}
136623 344 2021-05-14 40.1 mL/min/{1.73_m2}
137221 344 2021-05-28 50.8 mL/min/{1.73_m2}
136181 344 2021-06-18 47.8 mL/min/{1.73_m2}
137511 344 2021-07-23 39.4 mL/min/{1.73_m2}
136418 344 2021-08-13 54.9 mL/min/{1.73_m2}
135049 344 2021-09-03 48.6 mL/min/{1.73_m2}
135987 344 2021-11-19 41.2 mL/min/{1.73_m2}
135160 344 2022-02-04 40.4 mL/min/{1.73_m2}
135144 344 2022-04-01 38.6 mL/min/{1.73_m2}
135349 344 2022-05-06 50.3 mL/min/{1.73_m2}
136714 344 2022-06-03 58.9 mL/min/{1.73_m2}
135754 344 2022-09-09 40.6 mL/min/{1.73_m2}
137082 344 2022-09-30 36.6 mL/min/{1.73_m2}

Produce events for Atlas

One approach to use Phea’s results inside Atlas is to produce novel records (e.g. new rows in OBSERVATION or MEASUREMENT) using custom concept IDs, then ETL those back into the dataset.

Below I illustrate how to do it with case A, assuming the new records will go into MEASUREMENT. Notice I put the difference inside value_as_number, just in case.

case_a_custom_concept_id <- 2000000001
# Case A: SCr increase by >=0.3 over 48 hours
data_for_etl <- scr_change |>
  filter(scr_case_a) |> # Keep only rows where case A was TRUE
  transmute(
    measurement_datetime = ts,
    person_id = pid,
    value_as_number = scr_value_as_number - scr_48h_min_value_as_number,
    measurement_concept_id = case_a_custom_concept_id)

head_shot(data_for_etl) |>
  kable()
measurement_datetime person_id value_as_number measurement_concept_id
2016-01-29 3 0.5 2000000001
2016-11-11 3 1.7 2000000001
2016-11-27 19 1.6 2000000001
2018-12-16 19 0.8 2000000001
2019-11-03 19 1.0 2000000001
2020-11-22 19 0.6 2000000001
2021-11-14 19 0.8 2000000001
2021-02-21 30 0.4 2000000001
2021-02-23 30 0.3 2000000001
2021-02-24 30 0.3 2000000001

Author contact

Fabrício Kury – Please be always welcome to reach me at .